package com.etp.util;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import com.etp.entity.ImageInfo;
import com.etp.entity.Product;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.sl.usermodel.TextParagraph;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.sl.usermodel.PictureData;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xslf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.awt.*;
import java.awt.geom.Dimension2D;
import java.awt.geom.Rectangle2D;
import java.io.FileOutputStream;
import java.io.Writer;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;

import java.io.File;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelHelper {

    private Sheet sheet;        // Excel的工作表

    private File outputDir;     // 输出主目录

    private int photoCellIndex = 2;     // 主图片的列序

    public ExcelHelper(Sheet sheet,File outputDir) {
        this.sheet = sheet;
        this.outputDir = outputDir;
    }

    /**
     * 开始执行 Excel 到 PPT
     */
    public JsonResult execExcelToPPT() {

        /**
         * 取出所有的图片列表
         */
        List<Product> productList = readSheetAndLoadProductList();

        /**
         * 读取所有的图片
         */
        List<ImageInfo> imageInfoList = getImageInfoList();

        /**
         * 遍历并设置每个产品的产品图片，以及颜色图片列表
         */
        if(CollectionUtil.isNotEmpty(productList)) {

            // 遍历所有的产品列表
            productList.stream().forEach(product -> {

                int rowNum = product.getRowNum();   // 取出产品的行号
                List<ImageInfo> productImageList = imageInfoList.stream().filter(imageInfo -> {
                    int imageRowNum = imageInfo.getImageRowNum();     // 取出图片的行号
                    return rowNum == imageRowNum;                     // 只保留行号相同的图片
                }).collect(Collectors.toList());

                if(CollectionUtil.isNotEmpty(productImageList)) {       // 如果当前的产品有图片列表
                    List<ImageInfo> colorImageInfoList = new ArrayList<>();
                    for(int i = 0; i < productImageList.size(); i++) {
                        ImageInfo pImg = productImageList.get(i);
                        if(pImg.getImageColNum() == photoCellIndex) {          // 这是产品主图
                            //product.setProductImageBytes(pImg.getImageBytes());
                            product.setProductImageInfo(pImg);
                        } else {
                            colorImageInfoList.add(pImg);
                        }
                    }
                    product.setColorImageList(colorImageInfoList);
                }

            });

        }

        writeProductToPPT(productList);

        JSONObject jsonData = new JSONObject();

        jsonData.set("productCount",productList.size())     // 设置产品数量
                .set("imageCount",imageInfoList.size());    // 设置图片的数量

        return JsonResult.Success("执行execExcelToPPT成功").setData(jsonData);
    }


    /**
     * 将产品信息，输出到PPT文件
     *
     * @param productList
     */
    private void writeProductToPPT(List<Product> productList) {
        // 如果产品列表为空，则直接返回
        if(CollectionUtil.isEmpty(productList)) {
            return;
        }

        /**
         * 创建PPT对象
         */
        XMLSlideShow ppt = new XMLSlideShow();
        int x = 540;
        int y = 960;
        ppt.setPageSize(new Dimension(x,y));
        System.out.println("开始输出产品信息");
        int pIndex = 1;
        productList.stream().forEach(product -> {
            int rowNum = product.getRowNum();
            String ref = product.getRef();                          // 参考编号或标识符
            String description = product.getDescription();          // 描述
            Double prixNetUsd = product.getPrixNetUsd();            // 美元净价，不含附加费用，PRIX
            Double cbm = product.getCbm();                          // 在国际贸易和物流中 CBM（Cubic Meter） 是立方米 的体积单位
            Double moq = product.getMoq();                          // MOQ（Minimum Order Quantity）是国际贸易中的核心术语，全称 Minimum Order Quantity，中文译为 最小订单量，指供应商接受订单的最低货物数量要求。
            String color = product.getColor();                      // 颜色描述
            String size = product.getSize();                        // 尺寸
            String col = product.getCol();                          // 装箱率

            System.out.println("输出产品行号：" + rowNum + "，产品描述为：" + description);

            /**
             * （1）创建新的幻灯片
             */
            XSLFSlide slide = ppt.createSlide();

            // 取出描述占用的行数
            int descriptionLineCount = getLineCountByContent(description);
            int colorTextLineCount = getLineCountByContent(color);
            int totalLineCount = descriptionLineCount + colorTextLineCount + 6;       // 取出描述部分的总的行数
            System.out.println("描述占用的行数-------！" + descriptionLineCount + ", 颜色占用行数：" + colorTextLineCount + ",总行数：" + totalLineCount);

            double photoH = 300;            // 产品默认高度
            double colorH = 160;             // 颜色的高度统一为80


            /**
             * (2) 设置主产品图片
             */
            ImageInfo productImageInfo = product.getProductImageInfo();              //      主产品的图片
            List<ImageInfo> colorImageList = product.getColorImageList();            //       颜色的图片列表

            /**
             * 理论总高度
             */
            int totalHeight = 0;
            if(productImageInfo != null) {
                totalHeight += photoH;
                totalHeight += 40;
            }
            if(CollectionUtil.isNotEmpty(colorImageList)) {
                totalHeight += colorH + 40;
            }
            totalHeight += totalLineCount * 22;

            double startY = (y - totalHeight)/2.0;      // 开始的Y

            if(productImageInfo != null && productImageInfo.getImageBytes().length > 0) {

                double productDisplayWidth = productImageInfo.getDisplayWidth();      // 产品主图片的显示宽度
                double productDisplayHeight = productImageInfo.getDisplayHeight();    // 产品主图片的显示高度
                double w = NumberUtil.div(photoH * productDisplayWidth,productDisplayHeight,0);
                if(w > 540) {
                    w = 540;
                }

                // 定义主产品图片数据
                PictureData mainPicData = ppt.addPicture(productImageInfo.getImageBytes(), PictureData.PictureType.PNG);
                // 设置主图的位置（顶部居中）
                XSLFPictureShape mainPicture = slide.createPicture(mainPicData);
                mainPicture.setAnchor(new Rectangle2D.Double(
                        (x-w)/2.0,    // X坐标居中
                        startY,                   // Y坐标距顶部 50 像素
                        w,                      // 宽度 320 像素
                        photoH                  // 高度 160 像素
                ));
            }


            /**
             * (3) 添加文字描述
             */
            XSLFTextBox textBox = slide.createTextBox();
            int textBoxHeight =  calculateTextBoxHeight(totalLineCount);       // 计算高度
            textBox.setAnchor(new Rectangle2D.Double(
                    (x-320)/2.0,    // X坐标居中
                    startY + photoH + 40,                   // Y坐标距顶部 50 像素
                    320,                  // 宽度 320 像素
                    textBoxHeight                   // 高度 160 像素
            ));

            // 构建文本
            String[] contentList = {
                    "$" + prixNetUsd,
                    "\n" + ref,
                    description,
                    /*String.valueOf(moq) + "MOQ",*/
                    "Size: " + size,
                    String.valueOf(cbm) + "CBM",
                    StrUtil.isNotBlank(col)?"COL: " + col:"",
                    StrUtil.isNotBlank(color)?"COLOUR: " + color:""
            };

            // 遍历输出
            for(int i = 0; i< contentList.length; i++) {
                XSLFTextParagraph textParagraph = textBox.addNewTextParagraph();
                textParagraph.setTextAlign(TextParagraph.TextAlign.CENTER);

                XSLFTextRun textRun = textParagraph.addNewTextRun();

                textRun.setFontFamily("Arial");
                textRun.setText(contentList[i]);
                textRun.setFontSize(16.0);
                textRun.setFontColor(Color.BLACK);
                if(i == 0) {
                    textRun.setFontSize(24.0);
                    textRun.setBold(true);
                }

            }


            /**
             * (4) 输出颜色图片
             */
            if(CollectionUtil.isNotEmpty(colorImageList)) {

                //int startY = 550;    // 起始Y坐标

                int spacing = 20;    // 图片间距

                /**
                 * 颜色图片的数量
                 */
                int colorImgCount = colorImageList.size();

                double totalWidth = totalColorImageWidth(colorImageList,colorH,spacing);
                double startX = (x - totalWidth) / 2.0;       // 从哪里开始X轴

                double nextStartX = startX;

                // 遍历并写入颜色图片
                for(int j = 0; j < colorImgCount; j++) {

                    ImageInfo colorImageInfo = colorImageList.get(j);
                    byte[] colorImgBytes = colorImageInfo.getImageBytes();

                    double colorW = NumberUtil.div(colorH * colorImageInfo.getDisplayWidth(),colorImageInfo.getDisplayHeight(),0);

                    PictureData colorPicData = ppt.addPicture(colorImgBytes, PictureData.PictureType.PNG);

                    XSLFPictureShape colorPic = slide.createPicture(colorPicData);

                    colorPic.setAnchor(new Rectangle2D.Double(
                            nextStartX,    // X坐标居中
                            startY + photoH + 40 + totalLineCount * 22 + 40,                   // Y坐标距顶部 50 像素
                            NumberUtil.div(colorH * colorImageInfo.getDisplayWidth(),colorImageInfo.getDisplayHeight(),0),                  // 宽度 320 像素
                            colorH                   // 高度 160 像素
                    ));

                    nextStartX = nextStartX + colorW + spacing;
                }

            }

        });

        /**
         * 开始保存文件
         */
        String currDate = DateUtil.format(new Date(),"yyyy-MM-dd");
        String currDateTime = DateUtil.format(new Date(),"yyyy-MM-dd-HHmmss");
        String outputDirAbsolutePath = outputDir.getAbsolutePath();    // 取出输出目录的绝对路径

        // 创建新的输出目录，即是在输出目录中，创建当前日期作为目录
        File writeDir = new File(outputDirAbsolutePath + "/" + currDate);
        if(!writeDir.exists()) {
            writeDir.mkdir();      // 创建目录
        }

        // 输出新的文件
        File pptFile = new File(writeDir.getAbsolutePath() + "/produt-" + currDateTime + ".pptx");
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(pptFile);
            ppt.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(ObjectUtil.isNotEmpty(out)) {
                try {
                    out.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

    }

    /**
     * 计算颜色应该的总宽度
     *
     * @param colorImageList
     *          图片的总量
     * @param colorH
     *          颜色图片的高度
     * @param spacing
     *          颜色图片之间的间隔
     * @return
     */
    private double totalColorImageWidth(List<ImageInfo> colorImageList,double colorH,double spacing) {

        double totalColorImageWidth = 0;

        for (int i = 0;i<colorImageList.size();i++) {
            ImageInfo colorImageInfo = colorImageList.get(i);
            double colorW = NumberUtil.div(colorH * colorImageInfo.getDisplayWidth(),colorImageInfo.getDisplayHeight(),0);   // 当前图片的宽度
            if(i == 0) {
                totalColorImageWidth = colorW;
            } else {
                totalColorImageWidth = totalColorImageWidth + spacing + colorW;
            }
        }

        return totalColorImageWidth;
    }

    private int calculateTextBoxHeight(int totalLineCount) {
        int textBoxHeight = 0;

        textBoxHeight = totalLineCount * 21;

        return textBoxHeight;
    }


    /**
     * 判断传入的 content，取出这个描述占用了几行 (产品描述有几个分行符：\\r 或是 \\n 或是 \\r\\n )
     *
     * @param content
     * @return
     */
    private int getLineCountByContent(String content) {
        int lineCount = 0;      // 行数
        if(StrUtil.isBlank(content)) {
            return 1;
        } else {

            String[] split = content.split("\\n");
            lineCount = split.length;      //     理论上，获取几个换行符，就有几行

            /**
             * 还需要再判断，分隔出来字符串。
             * 如果其宽度大于一行所占字符
             * 我们定的文本框的宽度为300，按这个来算
             * 大概是：
             */
            for (String s : split) {
                int length = s.length();

                double div = NumberUtil.div(length, 33, 0);
                if(div>1) {
                    lineCount += div;
                }
            }

        }

        return lineCount;
    }


    /**
     * 读取所有的图片，并返回列表
     * @return
     */
    private List<ImageInfo> getImageInfoList() {

        List<ImageInfo> imageInfoList = new ArrayList<>();
        if(ObjectUtil.isNotEmpty(sheet)) {

            XSSFDrawing xssfDrawing = (XSSFDrawing)sheet.getDrawingPatriarch();
            List<XSSFShape> shapeList = new ArrayList<>();
            shapeList = xssfDrawing.getShapes();

            if(CollectionUtil.isNotEmpty(shapeList)) {
                for(XSSFShape shape: shapeList) {
                    if(shape instanceof XSSFPicture) {
                        analysisImageInfoByXSSFShape(shape,imageInfoList);
                    } else if(shape instanceof XSSFShapeGroup) {
                        XSSFShapeGroup sGroup = (XSSFShapeGroup)shape;
                        XSSFClientAnchor anchor = (XSSFClientAnchor)sGroup.getAnchor();

                        for(XSSFShape subShape:sGroup) {
                            if(subShape instanceof XSSFPicture) {
                                analysisImageInfoByXSSFShapeForGroup(subShape,imageInfoList,anchor.getRow1() + 1,anchor.getCol1() + 1);
                            }
                        }
                        /*Iterator<XSSFShape> iterator = group.iterator();
                        while(iterator.hasNext()) {
                            XSSFShape xssfShape = iterator.next();
                            analysisImageInfoByXSSFShape(xssfShape,imageInfoList);
                        }*/

                    }
                }
            }


        }

        return imageInfoList;
    }


    private void analysisImageInfoByXSSFShape(XSSFShape shape,List<ImageInfo> imageInfoList) {
        if(ObjectUtil.isEmpty(shape)) {
            return;
        }

        if(shape instanceof XSSFPicture) {
            XSSFPicture picture = (XSSFPicture)shape;
            XSSFClientAnchor anchor = (XSSFClientAnchor)picture.getAnchor();

            // 读取图片所在的行号、列号
            int rowNum = anchor.getRow1() + 1;     // 图片左上角所在的行，行号从0开始，需要加1
            int colNum = anchor.getCol1() + 1;     // 图片左上角所在的列，列号从0开始，需要加1


            /**
             * 图片原始尺寸的计算方式如下：（暂时不以这个原始尺寸，以下面的显示尺寸）

             XSSFPicture picture = (XSSFPicture) shape;
             PictureData picData = picture.getPictureData();
             byte[] imageBytes = picData.getData();

             // 转换为 BufferedImage 获取原始尺寸
             BufferedImage image = ImageIO.read(new ByteArrayInputStream(imageBytes));
             int originalWidth = image.getWidth();
             int originalHeight = image.getHeight();

             */

            /**
             * 读取图片的显示尺寸
             *
             * 所谓的显示尺寸，是指直接从Excel读取图片时的尺寸，可以已经经过了修改，只是我们以读取Excel那个时刻的尺寸
             */
            Dimension2D imageDimension = picture.getImageDimension();
            double displayWidth = imageDimension.getWidth();          // 显示长，单位为点
            double displayHeight = imageDimension.getHeight();        // 显示高, 单位为点，一点约等于 1/72 英寸，转为像素：点 乘以 96/72

            byte[] pictureByteArr = picture.getPictureData().getData();

            ImageInfo imageInfo = new ImageInfo();
            imageInfo.setImageRowNum(rowNum);
            imageInfo.setImageColNum(colNum);
            imageInfo.setImageBytes(pictureByteArr);

            if(displayWidth <=0 || displayHeight <=0) {
                displayWidth = 1280;
                displayHeight = 1000;
            }

            imageInfo.setDisplayWidth(displayWidth);
            imageInfo.setDisplayHeight(displayHeight);

            System.out.println("图片行：" + rowNum + ",图片列：" + colNum + ",宽：" + displayWidth + ",高：" + displayHeight);
            if(displayWidth > 0 && displayWidth >0) {
                imageInfoList.add(imageInfo);
            }
        }

    }

    private void analysisImageInfoByXSSFShapeForGroup(XSSFShape shape,List<ImageInfo> imageInfoList,int rowNum,int colNum) {
        if(ObjectUtil.isEmpty(shape)) {
            return;
        }

        if(shape instanceof XSSFPicture) {
            XSSFPicture picture = (XSSFPicture)shape;
            XSSFClientAnchor anchor = (XSSFClientAnchor)picture.getAnchor();

            /**
             * 图片原始尺寸的计算方式如下：（暂时不以这个原始尺寸，以下面的显示尺寸）

             XSSFPicture picture = (XSSFPicture) shape;
             PictureData picData = picture.getPictureData();
             byte[] imageBytes = picData.getData();

             // 转换为 BufferedImage 获取原始尺寸
             BufferedImage image = ImageIO.read(new ByteArrayInputStream(imageBytes));
             int originalWidth = image.getWidth();
             int originalHeight = image.getHeight();

             */

            /**
             * 读取图片的显示尺寸
             *
             * 所谓的显示尺寸，是指直接从Excel读取图片时的尺寸，可以已经经过了修改，只是我们以读取Excel那个时刻的尺寸
             */
            Dimension2D imageDimension = picture.getImageDimension();
            double displayWidth = imageDimension.getWidth();          // 显示长，单位为点
            double displayHeight = imageDimension.getHeight();        // 显示高, 单位为点，一点约等于 1/72 英寸，转为像素：点 乘以 96/72

            byte[] pictureByteArr = picture.getPictureData().getData();

            ImageInfo imageInfo = new ImageInfo();
            imageInfo.setImageRowNum(rowNum);
            imageInfo.setImageColNum(colNum);
            imageInfo.setImageBytes(pictureByteArr);

            if(displayWidth <=0 || displayHeight <=0) {
                displayHeight = 1000;
                displayWidth = 1280;
            }

            imageInfo.setDisplayWidth(displayWidth);
            imageInfo.setDisplayHeight(displayHeight);

            System.out.println("图片行：" + rowNum + ",图片列：" + colNum + ",宽：" + displayWidth + ",高：" + displayHeight);
            if(displayWidth > 0 && displayHeight >0) {
                imageInfoList.add(imageInfo);
            }
        }

    }


    /**
     * 读取Excel的工作表，并加载产品列表
     *
     * @return
     */
    private List<Product> readSheetAndLoadProductList() {
        List<Product> productList = new ArrayList<>();

        /**
         * 如果工作表不为空
         */
        if(ObjectUtil.isNotEmpty(sheet)) {

            int i = 1;

            /**
             * 在读取产品数据之前，先取出列名行，根据列名行，知道需要获取哪一列
             */

            int refCellIndex = 0;              // 标识符列序,
            int descriptionCellIndex = 0;      // 描述列序
            int prixNetUsdCellIndex = 0;       // 净价美元列序
            int cbmCellIndex = 0;              // 体积列序
            int colorCellIndex = 0;            // 颜色列序
            int sizeCellIndex = 0;             // 尺寸列序
            int colCellIndex=0;                // 装箱率，是一个整数，


            Row columnNameRow = sheet.getRow(5);       // 取出列名行
            if(ObjectUtil.isNotEmpty(columnNameRow)) {
                for (Cell cell : columnNameRow) {
                    String cellValue = cell.getStringCellValue();
                    if(StrUtil.isNotBlank(cellValue)) {
                        cellValue = cellValue.trim();
                        int currIndex = cell.getColumnIndex();
                        if("ref".equalsIgnoreCase(cellValue)){
                            refCellIndex = currIndex;
                        }

                        if("description".equalsIgnoreCase(cellValue)) {
                            descriptionCellIndex = currIndex;
                        }

                        if("color".equalsIgnoreCase(cellValue)) {
                            colorCellIndex = currIndex;
                        }

                        if("cbm".equalsIgnoreCase(cellValue)) {
                            cbmCellIndex = currIndex;
                        }

                        if("photo".equalsIgnoreCase(cellValue)) {
                            photoCellIndex = currIndex + 1;
                        }

                        if("size".equalsIgnoreCase(cellValue)) {
                            sizeCellIndex = currIndex;
                        }

                        if("col".equalsIgnoreCase(cellValue)) {
                            colCellIndex = currIndex;
                        }

                        if(StrUtil.containsAnyIgnoreCase(cellValue,"PRIX") && StrUtil.containsAnyIgnoreCase(cellValue,"NET") && StrUtil.containsAnyIgnoreCase(cellValue,"USD")) {
                            prixNetUsdCellIndex = currIndex;
                        }

                    }
                }
            }

            for(Row row: sheet) {

                int rowNum = row.getRowNum() + 1;     // 取出当前数据的所属行号，由于行号从0开始，为了与文档的序号匹配，获取到的行号，增加一

                /**
                 * 下面读取对应的属性
                 */
                // （1）读取标识符
                String ref = null;
                Cell refCell = row.getCell(refCellIndex);
                if(ObjectUtil.isNotEmpty(refCell)) {
                    try {
                        CellType refCellType = refCell.getCellType();
                        String name = refCellType.name();
                        if("NUMERIC".equalsIgnoreCase(name)) {
                            ref =String.valueOf(refCell.getNumericCellValue());
                        } else {
                            ref = refCell.getStringCellValue();
                        }
                    } catch (Exception e){};
                }

                // (2) 读取描述、
                String description = null;
                Cell descriptionCell = row.getCell(descriptionCellIndex);
                if(ObjectUtil.isNotEmpty(descriptionCell)) {
                    try {
                        description = descriptionCell.getStringCellValue();
                    } catch (Exception e){};
                }

                // (3) 读取美元净价
                Double prixNetUsd = null;
                Cell prixNetUsdCell = row.getCell(prixNetUsdCellIndex);
                if(ObjectUtil.isNotEmpty(prixNetUsdCell)) {
                    try {
                        prixNetUsd = prixNetUsdCell.getNumericCellValue();
                        //System.out.print("原始的美元净价：" + prixNetUsd);
                        BigDecimal bigDecimal = new BigDecimal(String.valueOf(prixNetUsd)).setScale(2, RoundingMode.HALF_UP);
                        prixNetUsd = bigDecimal.doubleValue();
                        //System.out.println("保留两位小数净价：" + prixNetUsd);
                    } catch (Exception e){};
                }

                // (4) 读取体积
                Double cbm = null;
                Cell cbmCell = row.getCell(cbmCellIndex);
                if(ObjectUtil.isNotEmpty(cbmCell)) {
                    try {
                        cbm = cbmCell.getNumericCellValue();
                    } catch (Exception e){};
                }

                // (5) 读取MOQ,最小订单量
                /*Double moq = null;
                Cell moqCell = row.getCell(9);
                if(ObjectUtil.isNotEmpty(moqCell)) {
                    try {
                        moq = moqCell.getNumericCellValue();
                    } catch (Exception e){};
                }*/

                // (6) 读取颜色描述
                String color = null;
                Cell colorCell = row.getCell(colorCellIndex);
                if(ObjectUtil.isNotEmpty(colorCell)) {
                    try {
                        color = colorCell.getStringCellValue();
                    } catch (Exception e){};
                }

                // (7) 读取尺寸
                String size = null;
                Cell sizeCell = row.getCell(sizeCellIndex);
                if(ObjectUtil.isNotEmpty(sizeCell)) {
                    try {
                        size = sizeCell.getStringCellValue();
                    } catch (Exception e){};
                }

                // (8) 读取装箱率
                String col = null;
                Cell colCell = row.getCell(colCellIndex);
                if(ObjectUtil.isNotEmpty(colCell)) {
                    try {
                        CellType colCellType = colCell.getCellType();
                        String name = colCellType.name();
                        if("NUMERIC".equalsIgnoreCase(name)) {
                            col =String.valueOf((int)colCell.getNumericCellValue());
                        } else {
                            col = colCell.getStringCellValue();
                        }
                    } catch (Exception e){};
                }

                if(StrUtil.isNotBlank(ref) && StrUtil.isNotBlank(description) && !"ref".equalsIgnoreCase(ref)) {

                    Product product = new Product();
                    product.setRowNum( rowNum);
                    product.setRef(ref);
                    product.setDescription(description);
                    product.setPrixNetUsd(prixNetUsd);
                    product.setCbm(cbm);
                    //product.setMoq(moq);
                    product.setColor(color);
                    product.setSize(size);
                    product.setCol(col);

                    productList.add(product);
                }

            }

        }

        return productList;
    }

}
